package util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;

import sun.jdbc.rowset.CachedRowSet;

public class mysql {
    private Connection conn = null;
    private Statement ps = null;
    private ResultSet rs = null;
    private PreparedStatement prs = null;

    /**
	 * 
	 * 方法描述：获取数据源连接池 参数： 无 返回值：Connection
	 */
    public Connection getConnection() throws SQLException {
        try {
            String userName = "root";
            String userPassword = "62727983";
            String url = "jdbc:mysql://127.0.0.1:3306/dishui?characterEncoding=UTF-8&characterSetResults=UTF-8&zeroDateTimeBehavior=convertToNull"; //10.155.86.13
            Class.forName("com.mysql.jdbc.Driver");
            return DriverManager.getConnection(url, userName, userPassword);
        } catch(Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
	 * 
	 * 方法描述：执行sql语句 参数：String sql 需要执行的sql语句 返回值：
	 */
    public void execute(String sql) throws SQLException {
        try {
            if (sql.contains("drop") || sql.contains("truncate")) {} else {
                conn = this.getConnection();
                ps = conn.createStatement();
                ps.execute(sql);
                ps.close();
            }
        } catch(SQLException e) {
            throw new SQLException(e.getMessage() + "出错的sql为:" + sql);
        } finally {
            clean();
        }
    }

    /**
	 * 
	 * 方法描述：批量执行数据库操作 参数：ArrayList， 由sql语句组成的ArrayList 返回值：无
	 */
    @SuppressWarnings({
        "unused",
        "unchecked"
    }) public void execute(ArrayList sqlList) throws SQLException {
        String sql = "";
        try {
            if (sql.indexOf("drop") > -1 || sql.indexOf("truncate") > -1) {} else {
                boolean autostatus;
                conn = this.getConnection();
                autostatus = conn.getAutoCommit();
                conn.setAutoCommit(false);
                ps = conn.createStatement();
                Iterator it = sqlList.iterator();
                while (it.hasNext()) {
                    sql = (String) it.next();
                    ps.execute(sql);
                }
                ps.close();
                if (autostatus) {
                    conn.commit();
                }
            }
        } catch(SQLException e) {
        	System.err.println("出错的是：   " +sql);
            conn.rollback();
            throw e;
        } finally {
            clean();
        }
    }

    /**
	 * 
	 * @Transaction
	 * 
	 */
    public void startTransaction() throws SQLException {
        if (conn != null) {
            conn.setAutoCommit(false);
        }
    }

    public void commit() {
        try {
            if (conn != null) {
                conn.commit();
            }
        } catch(SQLException e) {}
    }

    public void rollback() {
        try {
            if (conn != null) {
                conn.rollback();
            }
        } catch(SQLException e) {}
    }

    public void clean() {
        if (this.prs != null) {
            try {
                this.prs.close();
            } catch(SQLException e) {} finally {
                this.prs = null;
            }
        }
        if (this.rs != null) {
            try {
                this.rs.close();
            } catch(SQLException e) {} finally {
                this.rs = null;
            }
        }
        if (this.ps != null) {
            try {
                this.ps.close();
            } catch(SQLException e) {} finally {
                this.ps = null;
            }
        }
        if (this.conn != null) {
            try {
                this.conn.close();
            } catch(SQLException e) {} finally {
                this.conn = null;
            }
        }
    }

}